package com.example.demo.mapper;

import com.example.demo.entity.Grades;
import com.example.demo.entity.Huiyuan;
import com.example.demo.entity.ImgMessage;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface HuiyuanMapper {
    @Select("select * from huiyuan  where name=#{name}")
    public List<Huiyuan> getgradeusername(String name);

    @Select("select * from huiyuan ")
    public List<Huiyuan> getallgrade();

    @Insert("insert into huiyuan (name,sex,address,phone,memo,createtime,q,q1)values(#{name},#{sex},#{address},#{phone},#{memo},#{createtime},#{q},#{q1})")
    public void insertgrade(Huiyuan huiyuan);

    @Delete("delete from  huiyuan where id=#{id} ")
    public void deletegrade(int id);

    @Update("update  huiyuan set name=#{name},sex=#{sex},address=#{address},phone=#{phone},memo=#{memo},createtime=#{createtime},q=#{q},q1=#{q1} where id=#{id}")
    public void upgrade(Huiyuan huiyuan);

    @Select("select * from huiyuan  where id >= (select id from huiyuan order by id limit #{i} , 1)  ORDER BY id    limit #{pagesize} ")
    public List<Huiyuan> getpicfenye(int i, int pagesize);

    @Select("select * from huiyuan ")
    public List<Huiyuan> getpicfenyes();

    @Select("SELECT * FROM huiyuan WHERE `name` LIKE CONCAT('%',CONCAT(#{name},'%')) ")
    public List<Huiyuan> mohu(String name);

    @Select("SELECT * FROM huiyuan WHERE `q` LIKE CONCAT('%',CONCAT(#{q},'%')) ")
    public List<Huiyuan> mohu1(String name);

    @Select("SELECT * FROM huiyuan WHERE `q1` LIKE CONCAT('%',CONCAT(#{q1},'%')) ")
    public List<Huiyuan> mohu2(String name);


    @Select("select * from huiyuan where  name like CONCAT('%',CONCAT(#{name},'%')) and concat(sex) like CONCAT('%',CONCAT(#{sex},'%'))  and concat(sex) like '%%' and concat(q) like CONCAT('%',CONCAT(#{ab},'%')) and concat(address) like CONCAT('%',CONCAT(#{address},'%')) and concat(memo) like CONCAT('%',CONCAT(#{memo},'%')) limit #{ii}, #{pagesize}")

    public List<Huiyuan> mohu3(String name, String sex, String ab, String address, String phone, String memo, int ii, int pagesize);

    @Select("select * from huiyuan where name like CONCAT('%',CONCAT(#{name},'%')) and concat(sex) like CONCAT('%',CONCAT(#{sex},'%'))  and concat(sex) like '%%' and concat(q) like CONCAT('%',CONCAT(#{ab},'%'))and concat(address) like CONCAT('%',CONCAT(#{address},'%'))  and (memo IS NULL or memo  LIKE CONCAT('%',#{memo},'%'))")

    public List<Huiyuan> mohu3B(String name, String sex, String ab, String address, String phone, String memo, int ii, int pagesize);

//    @Select("SELECT * FROM huiyuan WHERE  (name LIKE CONCAT('%',CONCAT(#{name},'%'))" +
//            "OR name IS NULL OR name = '')" +
//            "AND (sex LIKE CONCAT('%',CONCAT(#{sex},'%')) OR sex IS NULL OR sex = '')" +
//            "AND (`q` LIKE CONCAT('%',CONCAT(#{q},'%')) OR q IS NULL OR q = '')" +
//            "AND (address LIKE CONCAT('%',CONCAT(#{address},'%')) OR address IS NULL OR address = '')" +
//            "AND (phone LIKE CONCAT('%',CONCAT(#{phone},'%')) OR phone IS NULL OR phone = '')" +
//            "AND (memo LIKE CONCAT('%',CONCAT(#{memo},'%')) OR memo IS NULL OR memo = '')     limit #{ii}, #{pagesize}")
//
//    public List<Huiyuan> mohu3(String name,String sex,String q,String address, String phone,String memo,int ii , int pagesize);


    @Select("SELECT * FROM huiyuan WHERE  (name LIKE CONCAT('%',CONCAT(#{name},'%')) " +
            "OR name IS NULL OR name = '') " +
            "AND (sex LIKE CONCAT('%',CONCAT(#{sex},'%')) OR sex IS NULL OR sex = '')" +
            " AND (address LIKE CONCAT('%',CONCAT(#{address},'%')) OR address IS NULL OR address = '') " +
            "AND (phone LIKE CONCAT('%',CONCAT(#{phone},'%')) OR phone IS NULL OR phone = '') " +
            "AND (memo LIKE CONCAT('%',CONCAT(#{memo},'%')) OR memo IS NULL OR memo = '') ")
    public List<Huiyuan> mohu3s(String name, String sex, String address, String q, String phone, String memo);

    @Select("SELECT * FROM huiyuan WHERE  (name LIKE CONCAT('%',CONCAT(#{name},'%')) " +
            "AND (sex LIKE CONCAT('%',CONCAT(#{sex},'%')))" +
            " AND (address LIKE CONCAT('%',CONCAT(#{address},'%')) ) " +
            "AND (phone LIKE CONCAT('%',CONCAT(#{phone},'%')) ) " +
            "AND (memo LIKE CONCAT('%',CONCAT(#{memo},'%')))")
    public List<Huiyuan> mohu4(String name, String sex, String address, String phone, String memo, int i, int pagesize);

    @Select("select * from huiyuan where name like CONCAT('%',CONCAT(#{name},'%')) and concat(sex) like CONCAT('%',CONCAT(#{sex},'%'))  and concat(sex) like '%%' and concat(q) like CONCAT('%',CONCAT(#{ab},'%')) and concat(address) like CONCAT('%',CONCAT(#{address},'%'))  and (memo IS NULL or memo  LIKE CONCAT('%',#{memo},'%')) limit #{ii}, #{pagesize}")

    public List<Huiyuan> mohu3ps(String name, String sex, String ab, String address, String phone, String memo, int ii, int pagesize);

    @Select("select * from huiyuan where name like CONCAT('%',CONCAT(#{name},'%')) and concat(sex) like CONCAT('%',CONCAT(#{sex},'%'))  and concat(sex) like '%%' and concat(q) like CONCAT('%',CONCAT(#{ab},'%')) and concat(address) like CONCAT('%',CONCAT(#{address},'%'))  and concat(memo) like CONCAT('%',CONCAT(#{memo},'%'))")

    public List<Huiyuan> mohu3pss(String name, String sex, String ab, String address, String phone, String memo, int ii, int pagesize);

    @Select("select * from huiyuan where id like CONCAT('%',CONCAT(#{id},'%')) limit #{i}, #{pagesize}")
    public List<Huiyuan> mohuid(Integer id, int i, int pagesize);

    @Select("select * from huiyuan where id like CONCAT('%',CONCAT(#{id},'%'))")
    public List<Huiyuan> mohuids(Integer id, int i, int pagesize);
}
